IF OBJECT_ID('tempdb..#TmpQuestionnaire') IS NOT NULL drop table #TmpQuestionnaire
CREATE TABLE #TmpQuestionnaire
(
OldQuestionnaireId INT NULL,
OldName VARCHAR(50) NULL,
OldDesc Varchar(250) NULL,
NewQuestionnaireId INT NULL,
NewName VARCHAR(50) NULL,
NewDesc Varchar(250) NULL
)

IF OBJECT_ID('tempdb..#TmpQuestionnaireNew') IS NOT NULL drop table #TmpQuestionnaireNew
CREATE TABLE #TmpQuestionnaireNew
(
NewQuestionnaireId INT NULL,
NewName VARCHAR(50) NULL,
NewDesc Varchar(250) NULL
)


INSERT INTO #TmpQuestionnaire(OldQuestionnaireId, OldName, OldDesc) 
SELECT Id, Name, Description FROM Insight_COI_QA.dbo.Questionnaire
WHERE type = 'QUESTTYPE_COI'

--SELECT * FROM #TmpQuestionnaire


INSERT INTO Questionnaire (Name, Type, Category, CategorySequenceNo, Description, AllowMultiple, ModuleType)
SELECT Name, Type, Category, CategorySequenceNo, Description, AllowMultiple, ModuleType FROM Insight_COI_QA.dbo.Questionnaire
WHERE type = 'QUESTTYPE_COI'


INSERT INTO #TmpQuestionnaireNew(NewQuestionnaireId, NewName, NewDesc) 
SELECT Id, Name, Description FROM Questionnaire
WHERE type = 'QUESTTYPE_COI'

--SELECT * FROM #TmpQuestionnaireNew


UPDATE #TmpQuestionnaire
	SET NewQuestionnaireId = TCN.NewQuestionnaireId,
		NewName = TCN.NewName,
		NewDesc = TCN.NewDesc
FROM #TmpQuestionnaire TC
INNER JOIN #TmpQuestionnaireNew TCN
	ON TC.OldName = TCN.NewName
	AND TC.OldDesc = TCN.NewDesc

--SELECT * FROM #TmpQuestionnaire


IF OBJECT_ID('tempdb..#TmpQVersion') IS NOT NULL drop table #TmpQVersion
CREATE TABLE #TmpQVersion
(
OldQversionId INT NULL,
OldQuestionnaireId INT NULL,
Version INT NULL,
NewVersionId INT NULL,
NewQuestionnaireId INT NULL,
)

IF OBJECT_ID('tempdb..#TmpQVersionNew') IS NOT NULL drop table #TmpQVersionNew
CREATE TABLE #TmpQVersionNew
(
NewVersionId INT NULL,
NewQuestionnaireId INT NULL,
Version INT NULL
)


INSERT INTO #TmpQVersion(OldQversionId,  OldQuestionnaireId, Version)
	SELECT Q.Id, Q.QuestionnaireID, Q.Version
	FROM Insight_COI_QA.dbo.QuestionnaireVersion Q
		WHERE Q.QuestionnaireId IN (SELECT OldQuestionnaireId FROM #TmpQuestionnaire)
		

--SELECT * FROM #TmpQVersion

UPDATE #TmpQVersion
SET NewQuestionnaireId = TQ.NewQuestionnaireId
FROM #TmpQVersion TQV
	INNER JOIN #TmpQuestionnaire TQ
ON TQV.OldQuestionnaireId = TQ.OldQuestionnaireId


INSERT INTO QuestionnaireVersion(Version, Active, QuestionnaireID, Description, Published)
	SELECT Q.Version, Q.Active, TQV.NewQuestionnaireID, Q.Description, Q.Published 
	FROM Insight_COI_QA.dbo.QuestionnaireVersion Q
		INNER JOIN #TmpQVersion TQV
		ON TQV.OldQversionId = Q.Id
		WHERE Q.QuestionnaireId IN (SELECT OldQuestionnaireId FROM #TmpQuestionnaire)



INSERT INTO #TmpQVersionNew(NewversionId,  NewQuestionnaireId, Version)
	SELECT Q.Id, Q.QuestionnaireID, Q.Version
	FROM QuestionnaireVersion Q
		WHERE Q.QuestionnaireId IN (SELECT NewQuestionnaireId FROM #TmpQuestionnaire)
		


UPDATE #TmpQVersion
	SET NewVersionId = TN.NewVersionId
FROM #TmpQVersion TQV
INNER JOIN #TmpQVersionNew TN
	ON TQV.NewQuestionnaireId = TN.NewQuestionnaireId
	AND TQV.Version = TN.Version


IF OBJECT_ID('tempdb..#TmpScript') IS NOT NULL drop table #TmpScript
CREATE TABLE #TmpScript
(
OldScriptId INT NULL,
ScriptName VARCHAR(40) NULL,
ScriptText text null,
ScriptType VARCHAR(50) NULL,
NewScriptId INT NULL,
ROWNUM INT IDENTITY(1,1)
)




INSERT INTO #TmpScript (OldScriptId, ScriptName, ScriptText, ScriptType)
SELECT Id, Name, ScriptText, Type FROM Insight_COI_QA.dbo.Script
WHERE Id IN ( SELECT ScriptId FROM Insight_COI_QA.dbo.Script_QuestionnaireVersion_Map
					WHERE QuestionnaireVersionId IN (SELECT OldQversionId FROM #TmpQVersion))



DECLARE @ScriptId INT
SELECT @ScriptId = IDENT_CURRENT('Script')

DECLARE @ID INT
SELECT @ID = COUNT(*) FROM #TmpScript

DECLARE @SINDEX INT
SET @SINDEX = 1

WHILE ( @SINDEX <= @ID)
BEGIN
UPDATE #TmpScript
SET NewScriptId = @ScriptId+@SINDEX
WHERE ROWNUM = @SINDEX
SET @SINDEX = @SINDEX+1
END


INSERT INTO Script(Name, ScriptText, Type)
SELECT Name, ScriptText, Type FROM Insight_COI_QA.dbo.Script
WHERE Id IN ( SELECT ScriptId FROM Insight_COI_QA.dbo.Script_QuestionnaireVersion_Map
					WHERE QuestionnaireVersionId IN (SELECT OldQversionId FROM #TmpQVersion))




INSERT INTO Script_QuestionnaireVersion_Map(ScriptId, QuestionnaireVersionId)
SELECT TS.NewScriptId,TQV.NewVersionId  FROM Insight_COI_QA.dbo.Script_QuestionnaireVersion_Map Map
INNER JOIN #TmpScript TS
ON Map.ScriptId = TS.OLdScriptId
INNER JOIN #TmpQVersion TQV
ON Map.QuestionnaireVersionId = TQV.OldQVersionId




IF OBJECT_ID('tempdb..#TmpQuestionnaireItem') IS NOT NULL drop table #TmpQuestionnaireItem
CREATE TABLE #TmpQuestionnaireItem 
	(ItemId int NULL,
	 NewItemId int NULL,
   	 VersionID int NULL,
	 NewVersionId int NULL,
	 ItemName varchar(30) NULL,
	 ItemText varchar(4000) NULL,
	 ValidationMessage varchar(250) NULL,
	 ItemType nvarchar(30)  NULL,
	 HasSeparator bit NULL,
	 ItemOrder int NULL,
	 ParentName varchar(30) NULL,
	 ParentValue varchar(30) NULL,
	 Required bit NULL ,
	 indent bit NULL ,
	 OptionsRefDomain varchar(50)  NULL)


INSERT INTO #TmpQuestionnaireItem (ItemId, VersionID, ItemName, ItemText, ValidationMessage, ItemType, HasSeparator, ItemOrder, ParentName, ParentValue, Required, indent, OptionsRefDomain)
SELECT Id, VersionID, ItemName, ItemText, ValidationMessage, ItemType, HasSeparator, ItemOrder, ParentName, ParentValue, Required, indent, OptionsRefDomain 
FROM  Insight_COI_QA.dbo.QuestionnaireItem
	WHERE VersionId >= (SELECT TOP 1 OldQversionId FROM #TmpQVersion)
	AND VersionId <= (SELECT MAX(OldQversionId) FROM #TmpQVersion)


UPDATE #TmpQuestionnaireItem
	SET NewVersionId = TQV.NewVersionId
FROM #TmpQuestionnaireItem TQI
INNER JOIN #TmpQVersion TQV
	ON TQI.VersionId = TQV.OldQversionId


INSERT INTO QuestionnaireItem(VersionID, ItemName, ItemText, ValidationMessage, ItemType, HasSeparator, ItemOrder, ParentName, ParentValue, Required, indent, OptionsRefDomain)
SELECT NewVersionID, ItemName, ItemText, ValidationMessage, ItemType, HasSeparator, ItemOrder, ParentName, ParentValue, Required, indent, OptionsRefDomain  FROM #TmpQuestionnaireItem

UPDATE #TmpQuestionnaireItem
	SET NewItemId = QI.Id
FROM #TmpQuestionnaireItem TQI
INNER JOIN QuestionnaireItem QI
	ON TQI.NewVersionId = QI.VersionId
	AND TQI.ItemName = QI.ItemName


IF OBJECT_ID('tempdb..#TmpItemProperty') IS NOT NULL drop table #TmpItemProperty
CREATE TABLE #TmpItemProperty
(
	ItemID int NULL,
	Attribute nvarchar(20) NULL,
	AttributeValue nvarchar(20) NULL,
)


INSERT INTO #TmpItemProperty 
SELECT Q.ItemId, Attribute, AttributeValue FROM Insight_COI_QA.dbo.ItemProperty Q
INNER JOIN #TmpQuestionnaireItem TQI
ON TQI.ItemId = Q.ItemId 
	WHERE Q.ItemId >= (SELECT top 1 ItemId FROM #TmpQuestionnaireItem)
									
					

UPDATE #TmpItemProperty 
	SET ItemId = TQI.NewItemId
FROM #TmpItemProperty TIP
INNER JOIN #TmpQuestionnaireItem TQI
	ON TIP.ItemId = TQI.ItemId



INSERT INTO ItemProperty
SELECT * FROM #TmpItemProperty


IF OBJECT_ID('tempdb..#TmpItemOption') IS NOT NULL drop table #TmpItemOption
CREATE TABLE #TmpItemOption
(
	ItemID int NULL,
	OptionText nvarchar(1000) NULL,
	OptionValue nvarchar(30) NULL
)


INSERT INTO #TmpItemOption
SELECT Q.ItemId, OptionText, OptionValue FROM Insight_COI_QA.dbo.ItemOption Q
INNER JOIN #TmpQuestionnaireItem TQI
ON TQI.ItemId = Q.ItemId 
	WHERE Q.ItemId >= (SELECT TOP 1 ItemId FROM #TmpQuestionnaireItem)

UPDATE #TmpItemOption 
	SET ItemId = TQI.NewItemId
FROM #TmpItemOption TIO
INNER JOIN #TmpQuestionnaireItem TQI
	ON TIO.ItemId = TQI.ItemId

INSERT INTO ItemOption
SELECT * FROM #TmpItemOption